package com.iris.live.services.data.repositories;

import com.iris.live.services.data.models.Lsh经销商表Model;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * Created by sxy on 2016/7/12
 */
public interface Lsh经销商表Repository extends JpaRepository<Lsh经销商表Model, String>, JpaSpecificationExecutor<Lsh经销商表Model> {
    @Query("select a from Lsh经销商表Model a where a.bmbs=?1 and (a.经销商状态 <> '已删除'or a.经销商状态 is null)")
    Lsh经销商表Model getLshDistributorByPermit(String permit);

    @Query("select distinct a from Lsh经销商表Model a where a.大区=?1 and (a.经销商状态 <> '已删除'or a.经销商状态 is null)")
    List<Lsh经销商表Model> findAllPermitBy大区(String area);

    @Query("select distinct a from Lsh经销商表Model a where a.小区=?1 and (a.经销商状态 <> '已删除'or a.经销商状态 is null)")
    List<Lsh经销商表Model> findAllPermitBy小区(String area);

    @Query("select distinct a from Lsh经销商表Model a where a.cta =?1 and (a.经销商状态 <> '已删除'or a.经销商状态 is null)")
    List<Lsh经销商表Model> findAllPermitByCTA(String area);

    @Query("select distinct a from Lsh经销商表Model a where a.经销商名 =?1 and (a.经销商状态 <> '已删除'or a.经销商状态 is null)")
    List<Lsh经销商表Model> findAllPermitBy他店(String area);

    @Query("select a from Lsh经销商表Model a where a.经销商名 =?1 and (a.经销商状态 <> '已删除'or a.经销商状态 is null)")
    List<Lsh经销商表Model> findAllPermitBy本店(String area);

    @Query("select  a from Lsh经销商表Model a where a.bmbs=?1")
    List<Lsh经销商表Model> findAllPermitBy本店1(String permit);

    @Query("select a from Lsh经销商表Model a where a.大区=?1 or a.小区=?1 or a.cta=?1 or a.经销商名=?1")
    List<Lsh经销商表Model> findAreaByParam(String area);

    @Query("select a from Lsh经销商表Model a where a.大区=?1 or a.经销商名=?1")
    List<Lsh经销商表Model> findDCHAreaByParam(String area);

    @Query("select a.经销商名 from Lsh经销商表Model a where a.bmbs=?1 ")
    String findNameByPermit(String id);

    @Query("select a from Lsh经销商表Model a where a.大区en=?1")
    List<Lsh经销商表Model> findSmallAreaByBigParam(String area);

    @Query("select  a from Lsh经销商表Model a where (a.经销商状态 <> '已删除'or a.经销商状态 is null) and a.是否测试<>'是'")
    List<Lsh经销商表Model> findAllPermit();

    @Query("select a.bmbs from Lsh经销商表Model a  where (a.经销商状态 <> '已删除'or a.经销商状态 is null) and a.是否测试<>'是'")
    List<String> findPermit();

    @Query("select a.大区 from Lsh经销商表Model a where a.bmbs = ?1")
    String getBigAreaByPermit(String permit);

    @Query("select distinct a.大区 from Lsh经销商表Model a")
    List<Lsh经销商表Model> getBigArea();

    @Query("select a from Lsh经销商表Model a  where  a.功能类型='AH' and   (a.经销商全称 is not null and a.经销商全称 <>'' )  ")
    List<Lsh经销商表Model> findPermitAndAh();

    @Query("select DISTINCT a.cta from Lsh经销商表Model a  where  a.cta is not null and a.cta <>''")
    List<String> getAllCTAlist();

    @Query("select  a from Lsh经销商表Model a  where  a.cta is not null and a.cta <>'' group by a.cta")
    List<Lsh经销商表Model> getListGroupByCta();

    @Query("select distinct  a.cta from Lsh经销商表Model a  where  a.大区= ?1 and a.cta is not null and a.cta<>'' ")
    List<String> findPermitAndAhBigArea(String dqu);

    @Query("select distinct   a.bmbs ,a.经销商名 from Lsh经销商表Model a  where  a.大区= ?1   ")
    List<String> findPermitAndAhCtaPermit(String dqu);

    @Query("select distinct  a.bmbs ,a.经销商名 from Lsh经销商表Model a  where  a.cta= ?1    ")
    List<String> findPermitAndAhBMBs(String dqu);

    @Query("select distinct  a.大区, a.大区en from Lsh经销商表Model a  where  a.大区  is not null and a.大区<>''   ")
    List<String> findBIgAreasLiSt();

    @Query("select distinct  a.bmbs ,a.经销商名,a.经销商名en from Lsh经销商表Model a     ")
    List<String> findbmbslist();

    @Query("select distinct  a.cta,a.ctaEn from Lsh经销商表Model a where a.cta is not null and a.cta<>''     ")
    List<String> findCTAlist();

    @Query(value="select distinct 大区_EN CODE from Lsh经销商表 a where 大区=?1", nativeQuery=true)
    List getBigAreaCode(String name);

    @Query(value="select distinct CTA_EN CODE from Lsh经销商表 a where CTA=?1", nativeQuery=true)
    List getCTACode(String name);

    @Query(value="select distinct 小区_EN CODE from Lsh经销商表 a where 小区=?1", nativeQuery=true)
    List getSmallAreaCode(String name);

    @Modifying
    @Transactional
    @Query(value = "update 顾客进店记录表 SET 离店时间 = 到店时间 ,停留时间 = 0 where 离店时间 is null and  到店时间 is not null and 进店日期 = DATE_SUB(CURDATE(),INTERVAL 1 day)",nativeQuery = true)
    void updateLeaveTime();

    @Query("select a from Lsh经销商表Model a where a.大区en=?1 and  a.ctaEn=?2  and a.是否测试<>'是' GROUP BY  a.lsh ")
    List<Lsh经销商表Model> findByBigAreCtaPermitList(String area,String cta);

    @Query("select a from Lsh经销商表Model a where a.大区en=?1   and a.是否测试<>'是' GROUP BY  a.lsh")
    List<Lsh经销商表Model> findByBigArePermitList(String area );

    @Query("select distinct  a.大区en from Lsh经销商表Model a  where  a.大区en  is not null and a.大区en<>''   ")
    List<String> getBIgAreasLiSt();

    @Query("select distinct  a.大区en from Lsh经销商表Model a  where  a.大区en  is not null and a.大区en<>''   ")
    List<String> getAllRegionEN();

    @Query("select distinct a.ctaEn  from Lsh经销商表Model a  where  a.ctaEn  is not null and a.ctaEn<>''   ")
    List<String> getAllPmaEN();

    @Query("select distinct a.lsh  from Lsh经销商表Model a  where  a.大区en=?1 and  a.lsh  is not null and a.lsh<>''  and a.是否测试<>'是'   ")
    List<String> getAllPermitEN(String  region);

    @Query("select a from Lsh经销商表Model a where a.lsh = ?1 ")
    List<Lsh经销商表Model> getDistributorByPermitLSH(String permitLSH);
}
